package com.treehouse.pornstar.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.PreparedStatement;
import com.treehouse.logging.Logger;
import com.treehouse.pornstar.Pornstar;
import com.treehouse.pornstar.dao.IPornstarDao;

public class PornstarDaoImpl implements IPornstarDao {

	private Connection connection;

	public PornstarDaoImpl(Connection connection) {
		this.connection = connection;
	}

	@Override
	public Pornstar selectPornstar(int id) {

		PreparedStatement statement = null;
		ResultSet result = null;
		Pornstar tmpPornstar = null;

		String query = "SELECT * FROM brazz_db.Pornstar ps WHERE ps.pornstar_id = ?";

		try {
			statement = (PreparedStatement) this.connection
					.prepareStatement(query);
			statement.setInt(1, id);
			result = statement.executeQuery();

			if (result.next()) {
				tmpPornstar = new Pornstar();
				tmpPornstar.setId(result.getInt("pornstar_id"));
				tmpPornstar.setName(result.getString("pornstar_name"));
				tmpPornstar.setDescription(result
						.getString("pornstar_description"));
				tmpPornstar.setRating(result.getDouble("pornstar_rating"));
				tmpPornstar.setHome(result.getURL("pornstar_home"));
			}

			statement.close();
			result.close();
		} catch (SQLException e) {
			Logger.err("Failed to select Pornstar by ID: " + id);
			e.printStackTrace(Logger.err);
		}

		return tmpPornstar;
	}

	@Override
	public Pornstar selectPornstar(String name) {
		PreparedStatement statement = null;
		ResultSet result = null;
		Pornstar tmpPornstar = null;

		String query = "SELECT * FROM brazz_db.Pornstar ps WHERE ps.pornstar_name = ?";

		try {
			statement = (PreparedStatement) this.connection
					.prepareStatement(query);
			statement.setString(1, name);
			result = statement.executeQuery();

			if (result.next()) {
				tmpPornstar = new Pornstar();
				tmpPornstar.setId(result.getInt("pornstar_id"));
				tmpPornstar.setName(result.getString("pornstar_name"));
				tmpPornstar.setDescription(result
						.getString("pornstar_description"));
				tmpPornstar.setRating(result.getDouble("pornstar_rating"));
				tmpPornstar.setHome(result.getURL("pornstar_home"));
			}

			statement.close();
			result.close();
		} catch (SQLException e) {
			Logger.err("Failed to select Pornstar by Name: " + name);
			e.printStackTrace(Logger.err);
		}

		return tmpPornstar;
	}

	@Override
	public Pornstar[] selectPornstars() {

		List<Pornstar> pornstars = null;
		Statement statement = null;
		ResultSet result = null;
		String query = "SELECT * FROM brazz_db.Pornstar ps";

		try {
			statement = this.connection.createStatement();
			result = statement.executeQuery(query);

			pornstars = new ArrayList<Pornstar>();

			while (result.next()) {
				Pornstar star = new Pornstar();
				star.setId(result.getInt("pornstar_id"));
				star.setName(result.getString("pornstar_name"));
				star.setDescription(result.getString("pornstar_description"));
				star.setRating(result.getDouble("pornstar_rating"));
				star.setHome(result.getURL("pornstar_home"));
				pornstars.add(star);
			}

			statement.close();
			result.close();

		} catch (SQLException e) {
			Logger.err("Failed to select all Pornstars!");
			e.printStackTrace(Logger.err);
		}

		return pornstars.toArray(new Pornstar[pornstars.size()]);
	}

	@Override
	public boolean insertPornstar(Pornstar pornstar) {

		PreparedStatement statement = null;

		String query = "INSERT INTO brazz_db.Pornstar (pornstar_id, pornstar_name, pornstar_description, pornstar_rating, pornstar_home, pornstar_age) "
				+ " VALUES (null, ?, ?, ?, ?, 0)";

		try {
			statement = (PreparedStatement) this.connection
					.prepareStatement(query);

			statement.setString(1, pornstar.getName());
			statement.setString(2, pornstar.getDescription());
			statement.setDouble(3, pornstar.getRating());
			statement.setURL(4, pornstar.getHome());

			int result = statement.executeUpdate();

			statement.close();

			if (result != Statement.EXECUTE_FAILED)
				return true;

		} catch (SQLException e) {
			Logger.err("Failed to insert a new pornstar for : "
					+ pornstar.getName());
			e.printStackTrace(Logger.err);
		}
		return false;
	}

	@Override
	public Pornstar[] selectPornstarsForScene(int id) {
		List<Pornstar> pornstars = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		String query = "SELECT ps.pornstar_id, ps.pornstar_name, ps.pornstar_description, ps.pornstar_rating, ps.pornstar_home, ps.pornstar_age "
				+ "FROM brazz_db.Pornstar ps "
				+ "INNER JOIN (brazz_db.Pornstar_Scene pssc) ON (ps.pornstar_id = pssc.pornstar_id) "
				+ "INNER JOIN (brazz_db.Pornstar_Scene pssc2, brazz_db.Scene sc) ON (sc.scene_id = pssc.scene_id) "
				+ "WHERE sc.scene_id = ?";

		try {
			statement = (PreparedStatement) this.connection
					.prepareStatement(query);

			statement.setInt(1, id);

			result = statement.executeQuery();

			pornstars = new ArrayList<Pornstar>();

			while (result.next()) {
				Pornstar star = new Pornstar();
				star.setId(result.getInt("pornstar_id"));
				star.setName(result.getString("pornstar_name"));
				star.setDescription(result.getString("pornstar_description"));
				star.setRating(result.getDouble("pornstar_rating"));
				star.setHome(result.getURL("pornstar_home"));
				pornstars.add(star);
			}

			return pornstars.toArray(new Pornstar[pornstars.size()]);

		} catch (SQLException e) {
			Logger.err("Failed to get all Pornstars by Scene ID: " + id);
			e.printStackTrace(Logger.err);
		}

		return null;
	}

	@Override
	public Pornstar[] selectPornstarsForScene(String name) {
		List<Pornstar> pornstars = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		String query = "SELECT ps.pornstar_id, ps.pornstar_name, ps.pornstar_description, ps.pornstar_rating, ps.pornstar_home, ps.pornstar_age "
				+ "FROM brazz_db.Pornstar ps "
				+ "INNER JOIN (brazz_db.Pornstar_Scene pssc) ON (ps.pornstar_id = pssc.pornstar_id) "
				+ "INNER JOIN (brazz_db.Pornstar_Scene pssc2, brazz_db.Scene sc) ON (sc.scene_id = pssc.scene_id) "
				+ "WHERE sc.scene_name = ?";

		try {
			statement = (PreparedStatement) this.connection
					.prepareStatement(query);

			statement.setString(1, name);

			result = statement.executeQuery();

			pornstars = new ArrayList<Pornstar>();

			while (result.next()) {
				Pornstar star = new Pornstar();
				star.setId(result.getInt("pornstar_id"));
				star.setName(result.getString("pornstar_name"));
				star.setDescription(result.getString("pornstar_description"));
				star.setRating(result.getDouble("pornstar_rating"));
				star.setHome(result.getURL("pornstar_home"));
				pornstars.add(star);
			}

			return pornstars.toArray(new Pornstar[pornstars.size()]);

		} catch (SQLException e) {
			Logger.err("Failed to get all Pornstars by Scene name: " + name);
			e.printStackTrace(Logger.err);
		}

		return null;
	}

}
